package com.taobao.ued.toy.dao;

import com.taobao.ued.toy.domain.Work;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


@Repository
public class JdbcWorkDao implements WorkDao {
	Log logger = LogFactory.getLog(getClass());
	private SimpleJdbcTemplate jdbcTemplate;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	private SimpleJdbcTemplate getSimpleJdbcTemplate() {
		return this.jdbcTemplate;
	}

	static String[] FIELDS = new String[]{
			"title",
			"designerId",
			"description",
			"taobaoLink",
			"cover",
			"pic"
	};

	static String[] PARAMS = DaoUtils.getFieldsForParams(FIELDS);


	public List<Work> getsByDesigner(String designerId, int start, int end) {
		List<Work> products = getSimpleJdbcTemplate().query(
				"select * from works where designerId= :designerId and cover !=1 limit :start , :end",
				new WorkMapper(),
				new MapSqlParameterSource()
						.addValue("designerId", designerId)
						.addValue("start", start)
						.addValue("end", end));
		return products;
	}

	public int add(Work d) throws Exception {
		logger.info("add Work: " + d.getTitle());

		MapSqlParameterSource params = DaoUtils.getParamSource(d, FIELDS);
		int count = getSimpleJdbcTemplate().update(
				"insert into works (" + StringUtils.join(FIELDS, ",") + ") " +
						"values (" + StringUtils.join(PARAMS, ",") + ")", params);

		logger.info("Rows affected: " + count);

		// 得到新加入一列的id
		return getSimpleJdbcTemplate().queryForInt("select LAST_INSERT_ID()");
	}

	public void delete(Work w) {
		logger.info("delete Work: " + w.getTitle());

		int count = getSimpleJdbcTemplate().update(
				"delete from works where id = :id",
				new MapSqlParameterSource().addValue("id", w.getId()));

		logger.info("Rows affected: " + count);
	}

	public void update(Work d) throws Exception {
		logger.info("update Work: " + d.getTitle());

		int count = getSimpleJdbcTemplate().update(
				"update works set " + DaoUtils.getUpdateParam(FIELDS) +
						" where id = :id",
				DaoUtils.getParamSource(d, FIELDS).addValue("id", d.getId()));

		logger.info("Rows affected: " + count);
	}

	public Work get(int id) {
		List<Work> products = getSimpleJdbcTemplate().query(
				"select * from works where id= :id",
				new WorkMapper(),
				new MapSqlParameterSource().addValue("id", id));
		if (products.size() > 0)
			return products.get(0);
		else
			return new Work();
	}

	public List<Work> getCoversByDesigner(String designerId) {
		List<Work> products = getSimpleJdbcTemplate().query(
				"select * from works where designerId= :designerId and cover = 1 ",
				new WorkMapper(),
				new MapSqlParameterSource()
						.addValue("designerId", designerId));
		return products;
	}

	public int getsByDesignerCount(String designerId) {
		return getSimpleJdbcTemplate().queryForInt(
				"select count(*) from works where designerId= :designerId and cover !=1",
				new MapSqlParameterSource()
						.addValue("designerId", designerId));
	}

	private static class WorkMapper implements ParameterizedRowMapper<Work> {

		public Work mapRow(ResultSet rs, int rowNum) throws SQLException {
			Work prod = new Work();
			prod.setId(rs.getInt("id"));
			try {
				DaoUtils.copyBeanFromRs(prod, rs, FIELDS);
			} catch (Exception e) {
				throw new SQLException(e);
			}
			return prod;
		}

	}

}